# @Project: diablo
# @File: insert_history_oe_data
# @Time: 2023/9/4 15:08
# @User: Jolly
# @Email: jolly@007vin.com

"""
oe历史译码定时写库，查询所有公司的询价单
"""

"""所需字段: vin、译码原始文字、oe号"""


import json
import copy
import pymysql
import time
from datetime import datetime
import threading
import traceback

from pymongo import MongoClient, InsertOne
from pymongo.errors import ConnectionFailure
from dbutils.pooled_db import PooledDB


lock = threading.Lock()


class InsertOeTransferHistoryData:
    """
    写入oe历史译码数据
    """

    def __init__(self):

        start_time = time.time()

        # self.merchant_id = merchant_id

        # 初始化数据库连接

        # 本地
        # self.diablo_conn_2_0 = self.get_mongo_connection("127.0.0.1", "27017")

        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 2.0测试库
        # self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.154", "27017")

        # 正式mysql
        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            # client = MongoClient(f"mongodb://{host}:{port}",
            #                      maxPoolSize=100,
            #                      minPoolSize=1,
            #                      maxIdleTimeMS=30000,
            #                      waitQueueTimeoutMS=2000)

            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=100,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        # 本地
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='127.0.0.1',
        #     user='root',
        #     port=3306,
        #     passwd='12345678',
        #     db='users',
        #     use_unicode=True)

        # 线上
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # 测试
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def date_time_to_timestamp(self, date_time_str):

        format_string = "%Y-%m-%d %H:%M:%S"

        # 将时间字符串解析为datetime对象
        datetime_obj = datetime.strptime(date_time_str, format_string)

        # 将datetime对象转换为时间戳（Unix时间戳，单位为秒）
        timestamp = datetime_obj.timestamp()

        return int(timestamp)

    def get_today_inquiry_data(self, merchant_id):
        """
        获取今日的询价单
        """
        connection = self.diablo_conn_2_0

        # 将日期时间对象格式化为字符串
        formatted_datetime = datetime.now().strftime('%Y-%m-%d') + " 00:00:00"
        # formatted_datetime = "2023-08-09 00:00:00"
        print(formatted_datetime)
        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "create_time": {
                        "$gt": formatted_datetime
                    }
                }
                docs = conn.find(filter_dict)

                if conn.count_documents(filter_dict) == 0:
                    return [], True

                data = [inquiry for inquiry in docs]

                return data, False

        except Exception as e:
            print("获取今日询价单数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def get_merchant_data(self):
        """
        获取所有商家信息，然后遍历写入看板数据
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 查询商家id，加入列表返回
            merchant_ids = []
            merchant_sql = f"select DISTINCT(merchant_id) merchant_id from diablo_merchant "
            cursor.execute(merchant_sql)
            merchant_data = cursor.fetchall()

            for merchant in merchant_data:
                merchant_ids.append(merchant[0])

            return merchant_ids

        except Exception as e:
            print(f"获取商家信息失败 {e}")
        finally:
            mysql_conn.close()

    def insert_oe_data(self, inquiry_data, merchant_id):
        """
        写入译码数据
        """

        # 拼接整合待写入译码数据
        formatted_datetime = datetime.now().strftime('%Y-%m-%d')

        operate_list = []

        for data in inquiry_data:

            bulk_insert_list = []

            # 只取报价并确认的订单
            inquiry_status = data.get("inquiry_status")
            if inquiry_status < 5:
                continue

            inquiry_id = data.get("inquiry_id")

            car_info = data.get("car_info")
            if not car_info:
                continue
            vin = car_info.get("vin")
            if not vin or vin == "":
                continue
            inquiry_info = data.get("inquiry_info")
            if not inquiry_info:
                continue

            part_list = data.get("part_list")
            if not part_list or len(part_list) == 0:
                continue

            for part in part_list:
                part_name = part.get("part_name")
                oe = part.get("oe")
                is_history = part.get("is_history", False)

                if part_name == "" or oe == "":
                    continue

                if is_history is True:
                    continue

                insert_dict = {
                    "merchant_id": merchant_id,
                    "inquiry_id": inquiry_id,
                    "vin": vin,
                    "oe": oe,
                    "part_name": part_name,
                    "transfer_date": formatted_datetime
                }

                bulk_insert_list.append(dict(insert_dict))

            for data in bulk_insert_list:
                operate_list.append(InsertOne(data))

        # 获取连接写入数据
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["transfer_oe_history"]

                conn.bulk_write(operate_list)

        except Exception as e:
            print("写入译码数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()


if __name__ == "__main__":
    # 获取当前写入时间

    service = InsertOeTransferHistoryData()

    # 获取商家id列表
    merchant_ids = service.get_merchant_data()
    if len(merchant_ids) == 0:
        print("未查询到商家id信息")

    for merchant_id in merchant_ids:

        inquiry_data, check = service.get_today_inquiry_data(merchant_id)
        if check:
            print(f"商家id{merchant_id}今日无询价单数据")
        else:

            # 写入译码数据
            service.insert_oe_data(inquiry_data, merchant_id)